
use T2Logging;
GO
-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_CATALOG = N'T2Logging' 
	 AND SPECIFIC_SCHEMA = N'dbo'
     AND SPECIFIC_NAME = N'Tenant_GetAll' 
)
   DROP PROCEDURE dbo.Tenant_GetAll
GO
create procedure dbo.Tenant_GetAll
    @PageSize int ,
    @PageNumber int, 
    @SortColumn nVarChar (100) ,
    @SortType nVarChar (4),
    @TotalRows int = null OUTPUT, 
	@RowsAffected int = null OUTPUT 
	
--*************************************************************
--Created By: trooks
--Name: dbo_Tenant_GetAll
--Table: Tenant
-- Schema: dbo
--Database: T2Logging
--Created Date: 02/15/2013 00:00:00
--*************************************************************

as
	Begin Try
        Declare @StartIndex int = (@PageNumber - 1) * @PageSize + 1
        Declare @EndingIndex int = (@PageSize - 1) * @PageNumber + @PageNumber 
        Set @TotalRows = (Select Count(*) from dbo.Tenant)
        IF @SortType = 'asc'
				BEGIN
					WITH tempTenant  AS
					(
                        SELECT	ROW_NUMBER() OVER (ORDER BY @SortColumn  asc ) AS RowNumber,
						TenantID, 
						TenantName, 
						TS, 
						CreatedBy, 
						CreatedDt, 
						ModifiedBy, 
						ModifiedDt 
      
                    FROM dbo.Tenant
                    )
                    SELECT	RowNumber, 
					TenantID, 
						TenantName, 
						TS, 
						CreatedBy, 
						CreatedDt, 
						ModifiedBy, 
						ModifiedDt 
 
					FROM	tempTenant
					WHERE	RowNumber BETWEEN @StartIndex AND @EndingIndex
					ORDER BY RowNumber ASC;
                    
		        END
			ELSE
		        BEGIN
					WITH tempTenant  AS
					(
                        SELECT	ROW_NUMBER() OVER (ORDER BY @SortColumn  desc ) AS RowNumber,
						TenantID, 
						TenantName, 
						TS, 
						CreatedBy, 
						CreatedDt, 
						ModifiedBy, 
						ModifiedDt 
      
                    FROM dbo.Tenant
                    )
                    SELECT	
					TenantID, 
						TenantName, 
						TS, 
						CreatedBy, 
						CreatedDt, 
						ModifiedBy, 
						ModifiedDt 
 
					FROM	tempTenant
					WHERE	RowNumber BETWEEN @StartIndex AND @EndingIndex
					ORDER BY RowNumber ASC;
                    
		        END
		

		Set @RowsAffected = @@ROWCOUNT;
		return 0;	
	END TRY
	BEGIN CATCH
		Set @RowsAffected = 0;
		EXEC dbo.uspLogError;
		return -1;
	END CATCH
	
GO
GRANT EXECUTE ON [T2Logging].[dbo].[Tenant_GetAll] to PORTAL_T2Logging
GO